package cn.taobaoke.restful.domain;

import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;
import org.joda.time.DateTime;
import org.joda.time.Days;
import org.joda.time.Months;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author yangzuo
 * @since 2017-03-27
 */
@Repository
public class StatisticsRepository {

    private Logger logger = LoggerFactory.getLogger(StatisticsRepository.class);

    @PersistenceContext
    private EntityManager entityManager;

    // TODO 注意状态选择: 是 支付成功, 结算,不包括: 支付失败,退款... (需要去阿里妈妈后台确认下)
    // TODO 订单结算, 订单付款, 订单失效, 订单成功

    // 管理员下级发展收入统计
    private static final String SUM_ADMIN_SUB_INCOME = "select a.*, count(o.id) as order_total, sum(o.admin_amount) as admin_income, sum(case when o.promoter_id = a.id then o.promoter_amount when o.parent_id = a.id then o.parent_amount else o.grand_parent_amount end ) as income from tb_agent a left join %s o on ( (a.id = o.grand_parent_id or a.id = o.parent_id or a.id = o.promoter_id) and o.order_status != '订单失效'  and DATE_FORMAT(o.create_time, '%%Y-%%m-%%d') BETWEEN DATE_FORMAT(:start, '%%Y-%%m-%%d') AND DATE_FORMAT(:end, '%%Y-%%m-%%d'))  where (a.is_delete is null or a.is_delete != true ) and ( a.name like :keyword or a.we_chat like :keyword or a.phone like :keyword ) and a.domain_id = :domainId group by a.id  limit :offset, :pageSize";
//    private static final String SUM_ADMIN_SUB_INCOME = "select a.*, count(o.id) as order_total, sum(o.admin_amount) as admin_income, sum(case when o.promoter_id = a.id then o.promoter_amount when o.parent_id = a.id then o.parent_amount else o.grand_parent_amount end ) as income from tb_agent a left join %s o on ( (a.id = o.grand_parent_id or a.id = o.parent_id or a.id = o.promoter_id) and o.order_status != '订单失效'  and DATE_FORMAT(o.create_time, '%Y-%m-%d') BETWEEN DATE_FORMAT(:start, '%Y-%m-%d') AND DATE_FORMAT(:end, '%Y-%m-%d'))  where (a.is_delete is null or a.is_delete != true ) and ( a.name like :keyword or a.we_chat like :keyword or a.phone like :keyword ) and a.domain_id = :domainId group by a.id  limit :offset, :pageSize";

    // 二级代理下面发展情况
    private static final String SUM_AGENT_SUB_INCOME = "select a.*, count(o.id) as order_total, sum(case when o.promoter_id = a.id then o.promoter_amount when o.parent_id = a.id then o.parent_amount else o.grand_parent_amount end ) as income, sum( case when o.promoter_id = a.id and a.parent_id = :agentId then o.parent_amount  when o.promoter_id = a.id and a.grand_parent_id = :agentId then o.grand_parent_amount when o.parent_id = a.id and a.parent_id = :agentId then o.grand_parent_amount else 0 end ) as commision_amount  from tb_agent a left join %s o on ( (a.id = o.promoter_id or a.id = o.parent_id or a.id = o.grand_parent_id) and o.order_status != '订单失效' and DATE_FORMAT(o.create_time, '%%Y-%%m-%%d') BETWEEN DATE_FORMAT(:start, '%%Y-%%m-%%d') AND DATE_FORMAT(:end, '%%Y-%%m-%%d') ) where (a.is_delete is null or a.is_delete != true) and ( a.parent_id = :agentId or a.grand_parent_id = :agentId ) and ( a.name like :keyword or a.we_chat like :keyword or a.phone like :keyword ) and a.domain_id = :domainId group by a.id limit :offset, :pageSize";
//    private static final String SUM_AGENT_SUB_INCOME = "select a.*, count(o.id) as order_total, sum(case when o.promoter_id = a.id then o.promoter_amount when o.parent_id = a.id then o.parent_amount else o.grand_parent_amount end ) as income, sum( case when o.promoter_id = a.id and a.parent_id = :agentId then o.parent_amount  when o.promoter_id = a.id and a.grand_parent_id = :agentId then o.grand_parent_amount when o.parent_id = a.id and a.parent_id = :agentId then o.grand_parent_amount else 0 end ) as commision_amount  from tb_agent a left join %s o on ( (a.id = o.promoter_id or a.id = o.parent_id or a.id = o.grand_parent_id) and o.order_status != '订单失效' and DATE_FORMAT(o.create_time, '%Y-%m-%d') BETWEEN DATE_FORMAT(:start, '%Y-%m-%d') AND DATE_FORMAT(:end, '%Y-%m-%d') ) where (a.is_delete is null or a.is_delete != true) and ( a.parent_id = :agentId or a.grand_parent_id = :agentId ) and ( a.name like :keyword or a.we_chat like :keyword or a.phone like :keyword ) and a.domain_id = :domainId group by a.id limit :offset, :pageSize";

    // 代理每日收入和订单数目情况
    private static final String SUM_AGENT_ORDER_INCOME = "select DATE_FORMAT(o.create_time, :dateFormat) AS 'date',  count(*) as total, sum(case when o.promoter_id = :agentId then o.promoter_amount when o.parent_id = :agentId then o.parent_amount else o.grand_parent_amount end ) as income  " +
            "  from %s o where ( o.promoter_id = :agentId  or o.parent_id = :agentId or o.grand_parent_id = :agentId ) and o.order_status != '订单失效' " +
            "and DATE_FORMAT(o.create_time, :dateFormat) BETWEEN DATE_FORMAT(:start, :dateFormat) AND DATE_FORMAT(:end, :dateFormat)  GROUP BY DATE_FORMAT(o.create_time, :dateFormat) order by DATE_FORMAT(o.create_time, :dateFormat) asc";

    // 管理员每日收入和订单数目情况
    private static final String SUM_ADMIN_ORDER_INCOME = "select DATE_FORMAT(o.create_time, :dateFormat) AS 'date',  count(*) as total,  sum(o.admin_amount) as income" +
            "  from %s o where o.order_status != '订单失效' " +
            "and DATE_FORMAT(o.create_time, :dateFormat) BETWEEN DATE_FORMAT(:start, :dateFormat) AND DATE_FORMAT(:end, :dateFormat)  GROUP BY DATE_FORMAT(o.create_time, :dateFormat) order by DATE_FORMAT(o.create_time, :dateFormat) asc";

    // 总代理统计
    // 管理员统计所有的代理, 一级代理统计二级代理个数
    private static final String COUNT_ADMIN_AGENT
            = "SELECT DATE_FORMAT(create_time, :dateFormat) as 'date', COUNT(*) AS count FROM tb_agent WHERE DATE_FORMAT(create_time, :dateFormat) BETWEEN DATE_FORMAT(:start, :dateFormat) AND DATE_FORMAT(:end, :dateFormat) and domain_id = :domainId group by DATE_FORMAT(create_time, :dateFormat) order by DATE_FORMAT(create_time, :dateFormat) asc";

    // 一级代理下面的代理个数
    private static final String COUNT_AGENT_AGENT
            = "SELECT DATE_FORMAT(a.create_time, :dateFormat) as date, COUNT(*) AS count FROM tb_agent a WHERE ( a.parent_id = :agentId or a.grand_parent_id = :agentId ) and DATE_FORMAT(a.create_time, :dateFormat) BETWEEN DATE_FORMAT(:start, :dateFormat) AND DATE_FORMAT(:end, :dateFormat) AND a.domain_id = :domainId group by DATE_FORMAT(a.create_time, :dateFormat) order by DATE_FORMAT(a.create_time, :dateFormat) asc";

    // 总会员统计
    // 统计管理员,也就是所有的会员数ene
    private static final String COUNT_ADMIN_MEMBER
            = "SELECT DATE_FORMAT(create_time, :dateFormat) as date, COUNT(*) AS count FROM tb_user WHERE DATE_FORMAT(create_time, :dateFormat) BETWEEN DATE_FORMAT(:start, :dateFormat) AND DATE_FORMAT(:end, :dateFormat) AND domain_id = :domainId group by DATE_FORMAT(create_time, :dateFormat) order by DATE_FORMAT(create_time, :dateFormat) asc";

    // 一级代理下面的成员
    private static final String COUNT_AGENT1_MEMBER
            = "SELECT DATE_FORMAT(u.create_time, :dateFormat) as date, COUNT(distinct u.id) AS count FROM tb_user u inner join tb_user_group_relation ur on (ur.user_id = u.id) inner join tb_agent_group_relation ar on (ur.group_id = ar.group_id ) inner join tb_agent ag on  ((ag.id = :agentId or ag.parent_id = :agentId or ag.grand_parent_id = :agentId ) and (ag.id = ar.agent_id or ag.parent_id = ar.agent_id or ag.grand_parent_id = ar.agent_id ))  WHERE DATE_FORMAT(u.create_time, :dateFormat) BETWEEN DATE_FORMAT(:start, :dateFormat) AND DATE_FORMAT(:end, :dateFormat) AND u.domain_id = :domainId group by DATE_FORMAT(u.create_time, :dateFormat) order by DATE_FORMAT(u.create_time, :dateFormat) asc";

    // eg: select DATE_FORMAT(o.create_time, '%Y-%m-%d') AS date,  count(o.id) as order_total, sum(o.admin_amount) as admin_income, sum(o.agent1amount) as agent1_income, sum(o.agent2amount) as agent2_income  from tb_order o where (o.agent1id in (1,5) or o.agent2id in (1,5)) and o.order_status != '订单失效' and DATE_FORMAT(o.create_time, '%Y-%m-%d') BETWEEN DATE_FORMAT('2015-03-01', '%Y-%m-%d') AND DATE_FORMAT('2017-03-27', '%Y-%m-%d')  GROUP BY DATE_FORMAT(o.create_time, '%Y-%m-%d') order by DATE_FORMAT(o.create_time, '%Y-%m-%d') asc
    // '%Y-%m-%d'
    DateTimeFormatter yyyyMMddFormat = DateTimeFormat.forPattern("yyyy-MM-dd");


    public List<Map> queryAgentSubAgentIncome(String keyword, Long agentId, String start, String end, Integer page, Integer size, String domain, Long domainId) {
        keyword = keyword == null ? "%%" : "%" + keyword + "%";

        Query query = entityManager.createNativeQuery(String.format(SUM_AGENT_SUB_INCOME, "tb_order_" + domain));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setParameter("keyword", keyword);
        query.setParameter("agentId", agentId);
        query.setParameter("start", start);
        query.setParameter("end", end);
        query.setParameter("offset", page * size);
        query.setParameter("pageSize", size);
        query.setParameter("domainId", domainId);

        return query.getResultList();
    }

    public List<Map> queryAdminSubAgentIncome(String keyword, String start, String end, Integer page, Integer size, String domain, Long domainId) {
        keyword = keyword == null ? "%%" : "%" + keyword + "%";
        Query query = entityManager.createNativeQuery(String.format(SUM_ADMIN_SUB_INCOME, "tb_order_" + domain));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setParameter("keyword", keyword);
        query.setParameter("start", start);
        query.setParameter("end", end);
        query.setParameter("offset", page * size);
        query.setParameter("pageSize", size);
        query.setParameter("domainId", domainId);

        logger.info("start => {} and end => {} and offset => {} and pageSize => {}", start, end, page * size, size);

        return query.getResultList();
    }

    public List<Map> queryAdminOrderAndIncome(String start, String end, String domain) {
        List<Map> mapList = executeSql(String.format(SUM_ADMIN_ORDER_INCOME, "tb_order_" + domain), start, end, null);
        setDefaultMapKeyValue(mapList, "total", 0);
        return setDefaultMapKeyValue(mapList, "income", 0);
    }

    public List<Map> queryAgentOrderAndIncome(Long agentId, String start, String end, String domain) {
        List<Map> mapList = executeSql(String.format(SUM_AGENT_ORDER_INCOME, "tb_order_" + domain), agentId, start, end, null);
        setDefaultMapKeyValue(mapList, "total", 0);
        return setDefaultMapKeyValue(mapList, "income", 0);
    }

    public List<Map> queryAdminSubAgent(String start, String end, Long domainId) {
        List<Map> mapList = executeSql(COUNT_ADMIN_AGENT, start, end, domainId);
        return setDefaultMapKeyValue(mapList, "count", 0);
    }

    public List<Map> queryAgentSubAgent(Long agentId, String start, String end, Long domainId) {
        List<Map> mapList = executeSql(COUNT_AGENT_AGENT, agentId, start, end, domainId);
        return setDefaultMapKeyValue(mapList, "count", 0);
    }

    public List<Map> queryAdminMember(String start, String end, Long domainId) {
        List<Map> mapList = executeSql(COUNT_ADMIN_MEMBER, start, end, domainId);
        return setDefaultMapKeyValue(mapList, "count", 0);
    }

    public List<Map> queryAgentMember(Long agentId, String start, String end, Long domainId) {
        List<Map> mapList = executeSql(COUNT_AGENT1_MEMBER, agentId, start, end, domainId);
        return setDefaultMapKeyValue(mapList, "count", 0);
    }

    private List<Map> executeSql(String sql, String start, String end, Long domainId) {
        return executeSql(sql, null, start, end, domainId);
    }

    private List<Map> setDefaultMapKeyValue(List<Map> mapList, String key, Object value) {
        if (mapList == null) return mapList;

        for (Map map : mapList) {
            if (!map.containsKey(key)) {
                map.put(key, value);
            }
        }

        return mapList;
    }


    // 查询date在中间
    private List<Map> executeSql(String sql, Long agentId, String start, String end, Long domainId) {
        if (end == null || end.isEmpty()) {
            end = DateTime.now().toString(yyyyMMddFormat);
        }
        if (start == null || start.isEmpty()) {
            start = DateTime.parse(end, yyyyMMddFormat).plusDays(-7).toString(yyyyMMddFormat);
        }

        logger.info("end => {}  start => {}", start, end);

        DateTime endDate = DateTime.parse(end, yyyyMMddFormat);
        DateTime startDate = DateTime.parse(start, yyyyMMddFormat);

        String format = "%Y-%m-%d";
        List<Map> tempMapList = new ArrayList<>();
        if (Math.abs(Days.daysBetween(endDate, startDate).getDays()) > 60) {  // 超过六十天,按照月份进行统计
            format = "%Y-%m";
            for (DateTime p = startDate; Months.monthsBetween(p, endDate).getMonths() > 0; p = p.plusMonths(1)) {
                HashMap<String, Object> map = new HashMap<>();
                map.put("date", p.toString("yyyy-MM"));
                tempMapList.add(map);
            }
            HashMap<String, Object> map = new HashMap<>();
            map.put("date", endDate.toString("yyyy-MM"));
            tempMapList.add(map);
        } else {
            for (DateTime p = startDate; Days.daysBetween(p, endDate).getDays() > 0; p = p.plusDays(1)) {
                HashMap<String, Object> map = new HashMap<>();
                map.put("date", p.toString("yyyy-MM-dd"));
                tempMapList.add(map);
            }
            HashMap<String, Object> map = new HashMap<>();
            map.put("date", endDate.toString("yyyy-MM-dd"));
            tempMapList.add(map);
        }
        // TODO yyyy-MM-dd  注意时间没有的的地方,显示空

        logger.info("format => {}, diff Days => {}", format, Days.daysBetween(endDate, startDate).getDays());

        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

        if (agentId != null) {
            query.setParameter("agentId", agentId);
        }
        if (domainId != null) {
            query.setParameter("domainId", domainId);
        }
        query.setParameter("dateFormat", format);
        query.setParameter("start", start);
        query.setParameter("end", end);
        List<Map> mapList = query.getResultList();

        HashMap<String, Map> dateMap = new HashMap<>();
        if (mapList != null) {
            for (Map map : mapList) {
                String date = (String) map.get("date");
                dateMap.put(date, map);
            }
        }

        List<Map> resultMapList = new ArrayList<>();
        for (Map map : tempMapList) {
            String date = (String) map.get("date");
            if (dateMap.containsKey(date)) {
                resultMapList.add(dateMap.get(date));
            } else {
                resultMapList.add(map);
            }
        }
        return resultMapList;
    }


    public Long countSubAgent(String keyword, Long agent1Id, Long domainId) {
        keyword = keyword == null ? "%%" : "%" + keyword + "%";
        Query query = entityManager.createNativeQuery("select count(*) as total from tb_agent a where (a.is_delete is null or a.is_delete != true ) and ( a.name like :keyword or a.we_chat like :keyword or a.phone like :keyword ) and a.parent_id = :parentId AND a.domain_id = :domainId");
//        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setParameter("parentId", agent1Id);
        query.setParameter("keyword", keyword);
        query.setParameter("domainId", domainId); // domainId
//        Map map = (Map) query.getSingleResult();
//        BigInteger total = (BigInteger) map.get("total");
        BigInteger total = (BigInteger) query.getSingleResult();
        return total.longValue();
    }

    public Long countAgent(String keyword, Long domainId) {
        keyword = keyword == null ? "%%" : "%" + keyword + "%";
        Query query = entityManager.createNativeQuery("SELECT COUNT(*) AS total FROM tb_agent a WHERE (a.is_delete IS NULL OR a.is_delete != true) AND ( a.name LIKE :keyword OR a.we_chat LIKE :keyword OR a.phone LIKE :keyword ) AND a.domain_id = :domainId");
//        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setParameter("keyword", keyword);
        query.setParameter("domainId", domainId); // domainId
//        Map map = (Map) query.getSingleResult();
//        BigInteger total = (BigInteger) map.get("total");
        BigInteger total = (BigInteger) query.getSingleResult();
        return total.longValue();
    }

}
